package xgenerator.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import xgenerator.constants.ConstraintTypes;
import xgenerator.constants.MySqlDataTypes;
import xgenerator.converter.MySqlDataTypeConverter;
import xgenerator.converter.PropertyTypeConverter;
import xgenerator.converter.DataTypeConverter;
import xgenerator.jdbc.JdbcContext;
import xgenerator.model.FieldMetadata;
import xgenerator.util.NotationUtils;
import xgenerator.util.StringUtils;

public class MetadataDaoMySqlImpl extends MetadataDaoJdbcAdapter {
	
	/**
	 * MySqlDataTypeConverter
	 */
	private final DataTypeConverter dataTypeConverter = new MySqlDataTypeConverter();
	
	/**
	 * JavaTypeConverter
	 */
	private PropertyTypeConverter propertyTypeConverter;

	/**
	 * <p>
	 * Title：获取所有表
	 * </p>
	 * @author <a href="mailto:shushanlee@msn.com">liss</a>
	 * @param dataSourceName
	 * @return
	 * @see xgenerator.dao.impl.MetadataDaoJdbcAdapter#getTables(java.lang.String)
	 */
	public Vector<String> getTables(String dataSourceName) {
		Vector<String> tableList = new Vector<String>();
		
		JdbcContext jdbcContext = JdbcContext.getJdbcContext(dataSourceName);
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = jdbcContext.currentConnection();
			String tableSchema = conn.getCatalog();
			stmt = conn.createStatement();
			String sql = "SELECT T.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_CATALOG='DEF' AND TABLE_SCHEMA='" + tableSchema + "' ORDER BY T.TABLE_NAME";
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				tableList.add(rs.getString("TABLE_NAME"));
			}
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			JdbcContext.close(stmt, rs);
		}
		
		return tableList;
	}

	/**
	 * <p>
	 * Title：获取表注释
	 * </p>
	 * @author <a href="mailto:shushanlee@msn.com">liss</a>
	 * @param dataSourceName
	 * @param tableName
	 * @return
	 * @see xgenerator.dao.impl.MetadataDaoJdbcAdapter#getTableComments(java.lang.String, java.lang.String)
	 */
	public String getTableComments(String dataSourceName, String tableName) {
		String tableComments = "";
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcContext.getJdbcContext(dataSourceName).currentConnection();
			String tableSchema = conn.getCatalog();
			stmt = conn.createStatement();
			String sql = "SELECT T.TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_CATALOG='DEF' AND TABLE_SCHEMA='" + tableSchema + "' AND TABLE_NAME='" + tableName + "'";
			rs = stmt.executeQuery(sql);
			
			if(rs.next()) {
				tableComments = rs.getString("TABLE_COMMENT");
			}
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			JdbcContext.close(stmt, rs);
		}
		
		return tableComments;
	}
	
	/**
	 * <p>
	 * Title：获取所有列
	 * </p>
	 * @author <a href="mailto:shushanlee@msn.com">liss</a>
	 * @param dataSourceName
	 * @param tableName
	 * @return
	 * @see xgenerator.dao.impl.MetadataDaoJdbcAdapter#getTableColumns(java.lang.String, java.lang.String)
	 */
	public Vector<String> getTableColumns(String dataSourceName, String tableName) {
		Vector<String> tableList = new Vector<String>();
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcContext.getJdbcContext(dataSourceName).currentConnection();
			String tableSchema = conn.getCatalog();
			stmt = conn.createStatement();
			String sql = "SELECT T.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS T WHERE T.TABLE_CATALOG='DEF' AND TABLE_SCHEMA='" + tableSchema + "' AND T.TABLE_NAME='" + tableName + "' ORDER BY T.COLUMN_NAME";
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				tableList.add(rs.getString("COLUMN_NAME"));
			}
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			JdbcContext.close(stmt, rs);
		}
		
		return tableList;
	}

	/**
	 * <p>
	 * Title：获取列元数据
	 * </p>
	 * @author <a href="mailto:shushanlee@msn.com">liss</a>
	 * @param dataSourceName
	 * @param tableName
	 * @return
	 * @see xgenerator.dao.impl.MetadataDaoJdbcAdapter#getTableColumnMetadata(java.lang.String, java.lang.String)
	 */
	public List<FieldMetadata> getTableColumnMetadata(String dataSourceName, String tableName) {
		List<FieldMetadata> tableList = new ArrayList<FieldMetadata>();
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcContext.getJdbcContext(dataSourceName).currentConnection();
			String tableSchema = conn.getCatalog();
			stmt = conn.createStatement();
			String sql = "SELECT T.ORDINAL_POSITION, T.COLUMN_NAME, T.COLUMN_COMMENT, T.COLUMN_DEFAULT, T.COLUMN_KEY, T.IS_NULLABLE, T.DATA_TYPE, T.CHARACTER_MAXIMUM_LENGTH, T.CHARACTER_OCTET_LENGTH, T.NUMERIC_PRECISION, T.NUMERIC_SCALE, T.COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS T WHERE T.TABLE_CATALOG='DEF' AND T.TABLE_SCHEMA='" + tableSchema + "' AND T.TABLE_NAME='" + tableName + "' ORDER BY T.ORDINAL_POSITION";
//			System.out.println(sql);
			rs = stmt.executeQuery(sql);
			
			FieldMetadata fieldMetadata = null;
			while(rs.next()) {
				fieldMetadata = new FieldMetadata();
				
				fieldMetadata.setColumnName(rs.getString("COLUMN_NAME"));
				fieldMetadata.setComments(rs.getString("COLUMN_COMMENT"));
				fieldMetadata.setDisplayName(rs.getString("COLUMN_COMMENT"));
				String dataType = rs.getString("DATA_TYPE");
				int dataLength = 0;
				int dataPrecision = 0;
				int dataScale = 0;
				if(MySqlDataTypes.INT_TYPE.equalsIgnoreCase(dataType)
						 || MySqlDataTypes.SMALLINT_TYPE.equalsIgnoreCase(dataType)
						 || MySqlDataTypes.MEDIUMINT_TYPE.equalsIgnoreCase(dataType)
						 || MySqlDataTypes.INT_TYPE.equalsIgnoreCase(dataType)
						 || MySqlDataTypes.BIGINT_TYPE.equalsIgnoreCase(dataType)
						 || MySqlDataTypes.FLOAT_TYPE.equalsIgnoreCase(dataType)
						 || MySqlDataTypes.DOUBLE_TYPE.equalsIgnoreCase(dataType)
						 || MySqlDataTypes.NUMERIC_TYPE.equalsIgnoreCase(dataType)
						 || MySqlDataTypes.DECIMAL_TYPE.equalsIgnoreCase(dataType)) {
					dataLength = 255;  //最大长度
					dataPrecision = rs.getInt("NUMERIC_PRECISION");  //实际长度
					dataScale = rs.getInt("NUMERIC_SCALE");
				} else if(MySqlDataTypes.CHAR_TYPE.equalsIgnoreCase(dataType) || MySqlDataTypes.VARCHAR_TYPE.equalsIgnoreCase(dataType)) {
					dataLength = rs.getInt("CHARACTER_OCTET_LENGTH");  //最大长度
					dataPrecision = rs.getInt("CHARACTER_MAXIMUM_LENGTH");  //实际长度
				}
				
				String sqlType = this.dataTypeConverter.convert(dataType, dataLength, dataPrecision, dataScale);
				fieldMetadata.setSqlDataType(sqlType);
				fieldMetadata.setDataLength(dataLength);
				fieldMetadata.setDataPrecision(dataPrecision);
				fieldMetadata.setDataScale(dataScale);
				String nullableFlag = rs.getString("IS_NULLABLE");
				if("YES".equals(nullableFlag)) {
					fieldMetadata.setNullable(true);
				} else {
					fieldMetadata.setNullable(false);
				}
				fieldMetadata.setDefaultLength(rs.getInt("CHARACTER_OCTET_LENGTH"));
				fieldMetadata.setDataDefault(rs.getString("COLUMN_DEFAULT"));
				fieldMetadata.setCharLength(rs.getInt("CHARACTER_MAXIMUM_LENGTH"));
				
				fieldMetadata.setPropertyName(NotationUtils.lowerCamelCase(rs.getString("COLUMN_NAME")));
				String propertyType = this.getPropertyTypeConverter().convert(sqlType);
				fieldMetadata.setPropertyType(propertyType);
				
				String constraintType = rs.getString("COLUMN_KEY");  // 约束类型
				if(StringUtils.isNotBlank(constraintType)) {
					fieldMetadata.setKeyField(true); // 是否为键
				} else {
					fieldMetadata.setKeyField(false); // 是否为键
				}
				if("PRI".equals(constraintType)) {
					fieldMetadata.setConstraintType(ConstraintTypes.PK_TYPE);
				} else if("REF".equals(constraintType)) {
					fieldMetadata.setConstraintType(ConstraintTypes.FK_TYPE);
					fieldMetadata.setReferencingTable(rs.getString("R_TABLE_NAME"));
					fieldMetadata.setReferencingColumn(rs.getString("R_COLUMN_NAME"));
					fieldMetadata.setDeleteRule(rs.getString("DELETE_RULE"));
				} else if("CHK".equals(constraintType)) {
					fieldMetadata.setConstraintType(ConstraintTypes.CHECK_TYPE);
				}
				
				tableList.add(fieldMetadata);
			}
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			JdbcContext.close(stmt, rs);
		}
		
		return tableList;
	}
	
	/**
	 * <p>
	 * Title：策略模式，设置属性类型转换策略
	 * </p>
	 * @param propertyTypeConverter
	 * @see xgenerator.dao.MetadataDao#setPropertyTypeConverter(xgenerator.converter.PropertyTypeConverter)
	 */
	public void setPropertyTypeConverter(PropertyTypeConverter propertyTypeConverter) {
		this.propertyTypeConverter = propertyTypeConverter;
	}
	
	/**
	 * @return the propertyTypeConverter
	 */
	public PropertyTypeConverter getPropertyTypeConverter() {
		return this.propertyTypeConverter;
	}
	
}
